CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."FNC_CAL_UP_DOWN_PERCENT" (p_date in varchar2, p_board_id in number) return number is
    result number;
    -- 用于存储p_board_date日的某一个版块的记录
    current_board_index_record board_index%rowtype;
    -- 用于记录p_board_date日的某一个版块的记录的数量
    current_board_index_record_num number;
    -- 用于存储p_board_date日的前一日的某一个版块的记录
    last_board_index_record board_index%rowtype;
    -- 用于存储p_board_date日的前一日的某一个版块的记录的数量
    last_board_index_record_num number;
begin
    result:=0;

    -- 查询p_board_date日的某一个版块的记录
    select count(*) into current_board_index_record_num from board_index t
                where t.date_=to_date(p_date,'yyyy-mm-dd') and t.board_id=p_board_id;
    if current_board_index_record_num<>0 then
        select * into current_board_index_record from board_index t
            where t.date_=to_date(p_date,'yyyy-mm-dd') and t.board_id=p_board_id;
    end if;

    -- 查询p_board_date日的前一日的某一个版块的记录
    select * into last_board_index_record_num from(
      select count(*) from board_index t where t.date_<to_date(p_date,'yyyy-mm-dd')
             and t.board_id=p_board_id order by t.date_ desc
    ) where rownum <=1;
    if last_board_index_record_num<>0 then
       select * into last_board_index_record from(
         select * from board_index t where t.date_<to_date(p_date,'yyyy-mm-dd')
             and t.board_id=p_board_id order by t.date_ desc
       ) where rownum <=1;
    end if;

    -- 更新UP_DOWN_PERCENTAGE列
    if current_board_index_record_num<>0 and last_board_index_record_num<>0 then
        update board_index t set t.up_down_percentage=(current_board_index_record.close_price-last_board_index_record.close_price)/last_board_index_record.close_price*100
               where t.board_id=p_board_id and t.date_=to_date(p_date,'yyyy-mm-dd');
        commit;
    end if;
    /*EXCEPTION WHEN OTHERS THEN
        result:=-1;
        DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
        DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm);
        ROLLBACK;*/

    return(result);
end FNC_CAL_UP_DOWN_PERCENT;